package br.unirio.webdisco.dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import br.unirio.webdisco.dao.conn.BasePostgresDAO;
import br.unirio.webdisco.entity.DNA;

public class DNAPostgresDAO extends BasePostgresDAO<DNA> {

	@Override
	public void incluir(DNA dna) throws Exception {
		Connection connection = criaConexao(false);
		String sql = "INSERT INTO DNA (IdDNA, IdUsuario, Sequencia) ";
			   sql += "VALUES (nextval('SEQ_IdDNA'),?,?);";
			   
			   
		PreparedStatement stmt = null;

		try {
			stmt = connection.prepareStatement(sql);
			stmt.setInt(1, dna.getIdUsuario());
			stmt.setString(2, dna.getSequencia());
			
			int retorno = stmt.executeUpdate();
			if (retorno == 0) {
				connection.rollback();
				throw new SQLException(
						"Ocorreu um erro inesperado no momento de inserir dados de local no banco!");
			}
			connection.commit();

		} catch (SQLException e) {
			connection.rollback();
			throw e;
		} finally {
			try {
				stmt.close();
				this.fechaConexao();
			} catch (SQLException e) {
				throw e;
			}

		}	
		
	}

	@Override
	public void alterar(DNA dna) throws Exception {
		Connection connection = criaConexao(false);
		String sql =  "UPDATE DNA SET IdUsuario = ?, Sequencia = ? ";
			   sql += "WHERE idDNA = ?";
			   
		PreparedStatement stmt = null;
		try {
			stmt = connection.prepareStatement(sql);
			stmt.setInt(1, dna.getIdUsuario());
			stmt.setString(2, dna.getSequencia());
			stmt.setInt(5, dna.getIdDNA());
			System.out.println(stmt);
			
			int retorno = stmt.executeUpdate();
			if (retorno == 0) {
				connection.rollback();
				throw new SQLException(
						"Ocorreu um erro inesperado no momento de alterar dados de local no banco!");
			}
			connection.commit();

		} catch (SQLException e) {
			connection.rollback();
			throw e;
		} finally {
			try {
				stmt.close();
				this.fechaConexao();
			} catch (SQLException e) {
				throw e;
			}

		}		
	}

	@Override
	public void excluir(DNA dna) throws Exception {
		Connection connection = criaConexao(false);
		String sql = "DELETE FROM dna WHERE IdUsuario = ?";
		PreparedStatement stmt = null;
		try {
			stmt = connection.prepareStatement(sql);
			stmt.setInt(1, dna.getIdUsuario());
			int retorno = stmt.executeUpdate();
			if (retorno == 0) {
				connection.rollback();
				throw new SQLException(
						"Ocorreu um erro inesperado no momento de remover dados de local no banco!");
			}
			connection.commit();

		} catch (SQLException e) {
			connection.rollback();
			throw e;
		} finally {
			try {
				stmt.close();
				this.fechaConexao();
			} catch (SQLException e) {
				throw e;
			}

		}
	}

	@Override
	public Integer count() throws Exception {
		Connection connection = criaConexao(false);
		String sql = "SELECT COUNT(*) AS Count FROM DNA ";
		
		
		PreparedStatement stmt = null;
		ResultSet rs = null;
		Integer countDNA = null;
		
		try {
			stmt = connection.prepareStatement(sql);
			rs = stmt.executeQuery();
			while (rs.next()) 	
				countDNA = rs.getInt("Count");
				
			
		} catch (SQLException e) {
			throw e;
		} finally {
			try {
				stmt.close();
				rs.close();
				this.fechaConexao();
			} catch (SQLException e) {
				throw e;
			}

		}
		return countDNA;
	}
	
	@Override
	public List<DNA> listar(Integer paginaTamanho, Integer paginaAtual) throws Exception {
		Connection connection = criaConexao(false);
		String sql = "SELECT * FROM DNA LIMIT ? OFFSET ?*?";
		
		
		PreparedStatement stmt = null;
		ResultSet rs = null;
		DNA dna= null;
		List<DNA> listaDNA = new ArrayList<DNA>();
		
		try {
			stmt = connection.prepareStatement(sql);
			stmt.setInt(1, paginaTamanho);
			stmt.setInt(2, paginaAtual);
			stmt.setInt(3, paginaTamanho);
			rs = stmt.executeQuery();
			while (rs.next()) 
			{
				dna = new DNA();
				dna.setIdDNA(rs.getInt("IdDNA"));
				dna.setIdUsuario(rs.getInt("IdUsuario"));
				dna.setSequencia(rs.getString("Sequencia"));
				
				listaDNA.add(dna);
				
			}
		} catch (SQLException e) {
			throw e;
		} finally {
			try {
				stmt.close();
				rs.close();
				this.fechaConexao();
			} catch (SQLException e) {
				throw e;
			}

		}
		return listaDNA;
	}
	

	@Override
	public DNA consultarPorId(Integer id) throws Exception {
		Connection connection = criaConexao(false);
		String sql = "SELECT * FROM DNA WHERE IdDNA = ? ";
		PreparedStatement stmt = null;
		ResultSet rs = null;
		DNA dna= null;
		try {
			stmt = connection.prepareStatement(sql);
			stmt.setInt(1, id);
			rs = stmt.executeQuery();
			if (rs.next()) {
				dna = new DNA();
				dna.setIdDNA(rs.getInt("IdDNA"));
				dna.setIdUsuario(rs.getInt("IdUsuario"));
				dna.setSequencia(rs.getString("Sequencia"));
				
			}
		} catch (SQLException e) {
			throw e;
		} finally {
			try {
			
				if(stmt != null)
					stmt.close();
				
				if(rs != null)
					rs.close();
				
				this.fechaConexao();

			} catch (SQLException e) {
				throw e;
			}

		}
		return dna;
	}

	public DNA consultarPorUsuario(Integer id) throws Exception {
		Connection connection = criaConexao(false);
		String sql = "SELECT * FROM DNA WHERE IdUsuario = ? ";
		PreparedStatement stmt = null;
		ResultSet rs = null;
		DNA dna= null;
		try {
			stmt = connection.prepareStatement(sql);
			stmt.setInt(1, id);
			rs = stmt.executeQuery();
			if (rs.next()) {
				dna = new DNA();
				dna.setIdDNA(rs.getInt("IdDNA"));
				dna.setIdUsuario(rs.getInt("IdUsuario"));
				dna.setSequencia(rs.getString("Sequencia"));
				
			}
			else return null;
		} catch (SQLException e) {
			throw e;
		} finally {
			try {
			
				if(stmt != null)
					stmt.close();
				
				if(rs != null)
					rs.close();
				
				this.fechaConexao();

			} catch (SQLException e) {
				throw e;
			}

		}
		return dna;
	}
	
	@Override
	public List<DNA> consultar(String parametros) throws Exception {
		Connection connection = criaConexao(false);
		String sql = "SELECT * FROM DNA WHERE 1 = 1 ";
		sql += parametros;
		
		PreparedStatement stmt = null;
		ResultSet rs = null;
		DNA dna= null;
		List<DNA> listaDNA = new ArrayList<DNA>();
		
		try {
			stmt = connection.prepareStatement(sql);
			rs = stmt.executeQuery();
			while (rs.next()) 
			{
				dna = new DNA();
				dna.setIdDNA(rs.getInt("IdDNA"));
				dna.setIdUsuario(rs.getInt("IdUsuario"));
				dna.setSequencia(rs.getString("Sequencia"));
				
				listaDNA.add(dna);
				
			}
		} catch (SQLException e) {
			throw e;
		} finally {
			try {
				stmt.close();
				rs.close();
				this.fechaConexao();
			} catch (SQLException e) {
				throw e;
			}

		}
		return listaDNA;
	}
	}

